''' 使用pymysql进行mysql数据库操作的工具类：
'   pip install PyMySQL
'   建立setting文件，加入数据库配置信息
'   使用时： from myDb import mydb， 具体用法参考__main__
'   Author： yueye 2024-01-10
'''
import pymysql
from setting  import db_config 

from my_utils.logger_util import log 

class MyDb:
    '''数据库的操作方法'''
    def __init__(self) -> None:
        self.host = db_config["host"]
        self.port=  db_config["port"]
        self.user =  db_config["user"]
        self.password=  db_config["password"]
        self.dbName=  db_config["dbName"]
        self.lockName = 'mylock'

        self.db = None
        self.cursor = None

    def connect(self):
        '''建立数据库连接'''
        if self.db and self.db.open:
            self.close()
        self.db = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbName, charset='utf8')

    def close(self):
        '''关闭数据库连接'''
        if self.db and self.db.open: 
            self.db.close()
            self.db = None

    def startTrans(self):
        '''该连接启动事务处理'''
        self.db.begin()

    def queryOne(self, sql, value=None):
        '''执行查询语句'''
        # 获取游标和连接
        cursor = self.cursor or self.db.cursor()
        try:
            # 执行sql语句
            count =cursor.execute(sql, value)  # 返回结果是受影响的行数
            rows = cursor.fetchone()
            return rows
        except Exception as ex:
            log.error("查询单条数据错误：%s", ex)
            return None
        finally:
            # 关闭游标和连接
            self.cursor or cursor.close()

    def query(self, sql, value=None):
        '''执行查询语句'''
        # 获取游标和连接
        cursor = self.cursor or self.db.cursor()
        try:
            # 执行sql语句
            count =cursor.execute(sql, value)  # 返回结果是受影响的行数
            rows = cursor.fetchall()
            return (count,rows)
        except Exception as ex:
            log.error("查询数据错误：%s", ex)
            return None
        finally:
            # 关闭游标和连接
            self.cursor or cursor.close()
 
    def execute(self, sql, value=None):
        """ 进行添加，修改操作 """
        # 获取游标和连接
        cursor = self.cursor or self.db.cursor()
        try:
            # 获取游标
            res = cursor.execute(sql, value)
            self.db.commit()
            return res
        except Exception as ex:
            self.db.rollback()
            log.error("执行sql错误：%s", ex)
            return None
        finally:
            # 关闭游标
            self.cursor or cursor.close()
    
    def executeMany(self, sql, valueTuple=None):
        """ 进行添加，修改操作 """
        # 获取游标和连接
        cursor = self.cursor or self.db.cursor()
        try:
            # 获取游标
            res = cursor.executemany(sql, valueTuple)
            self.db.commit()
            return res
        except Exception as ex:
            self.db.rollback()
            log.error("执行sql错误：%s", ex)
            return None
        finally:
            # 关闭游标
            self.cursor or cursor.close()
    
    def executeSqls(self, sqlList):
        """ 进行添加，修改操作 """
        # 获取游标和连接
        cursor = self.cursor or self.db.cursor()
        try:
            # 获取游标
            for sql in sqlList:
                res = cursor.execute(sql)
            self.db.commit()
            return res
        except Exception as ex:
            self.db.rollback()
            log.error("执行批量sql错误：%s", ex)
            return None
        finally:
            # 关闭游标
            self.cursor or cursor.close()

    def lock(self):
        '''为数据库加锁, 并标记加锁的cursor对象, 成功-1, 失败-0'''
        self.cursor = self.db.cursor()
        lockSql = f"SELECT GET_LOCK('{self.lockName}', 0)"
        lockRes = self.cursor.execute(lockSql)
        return lockRes

    def unlock(self):
        '''为数据库解锁'''
        unlockSql = f"SELECT RELEASE_LOCK('{self.lockName}')"
        unlockRes = self.cursor.execute(unlockSql)
        self.cursor.close()
        self.cursor = None
        return unlockRes

# 创建一个实例
mydb = MyDb()



